package classmate.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import classmate.dao.IRoomDao;
import classmate.db.DbConnection;
import classmate.po.Room;

public class RoomDaoImpl implements IRoomDao{

	@Override
	public void save(Room room) {
		Connection conn = DbConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "insert into room values(?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, room.getRoomNo());
			ps.setString(2, room.getRoomName());
			ps.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DbConnection.close(rs, ps);
		}
	}

	@Override
	public void update(Room room) {
		Connection conn = DbConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "update room,student set room_name = ? where room.room_no = ? and roon.room_no = student.room_no";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, room.getRoomNo());
			ps.setString(2, room.getRoomName());
			ps.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DbConnection.close(rs, ps);
		}
	}

	@Override
	public Room get(int index) {
		Connection conn = DbConnection.getConnection();
		List<Room> roomList = new ArrayList<Room>();
		String sql = "select room.room_no,room_name from room,student where room.room_no =? and room.room_no = student.room_no";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, index);
			rs = ps.executeQuery();
			while(rs.next()) {
				int roomNo = rs.getInt(1);
				String roomName = rs.getString(2);
				Room room = new Room(roomNo, roomName);
				roomList.add(room);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DbConnection.close(rs, ps);
		}
		return roomList.get(0);
	}

	@Override
	public void delete(int index) {
		Connection conn = DbConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "delete from room where room_no = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, index);
			ps.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DbConnection.close(rs, ps);
		}
		
	}

	@Override
	public List<Room> findAll() {
		Connection conn = DbConnection.getConnection();
		List<Room> roomlList = new ArrayList<Room>();
		String sql = "select student.room_no,room_name from room,student where room.room_no = student.room_no";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				int roomNo = rs.getInt(1);
				String roomName = rs.getString(2);
				Room room = new Room(roomNo, roomName);
				roomlList.add(room);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			DbConnection.close(rs, ps);
		}
		return roomlList;
	}
	
}
